package com.kma.ncpractice2013.dao;

import com.kma.ncpractice2013.model.PromoCode;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 *
 * @author Illya
 */
public class PromoCodeDAO extends DAO<PromoCode>
{
    private static final String INSERT_QUERY = "INSERT INTO PROMOCODES(CODE,DISCOUNT,START_DATE,EXPIRATION_DATE,USED) VALUES (?,?,?,?,?)";
    private static final String UPDATE_QUERY = "UPDATE PROMOCODES SET CODE = ?, DISCOUNT = ?, START_DATE = ?, EXPIRATION_DATE = ?, USED = ? WHERE ID = ?";
    private static final String SELECT_BY_ID_QUERY = "SELECT * FROM PROMOCODES WHERE ID = ?";
    private static final String SELECT_BY_CODE_QUERY = "SELECT * FROM PROMOCODES WHERE CODE = ?";
    private static final String SELECT_ALL_QUERY = "SELECT * FROM PROMOCODES";
    private static final String DELETE_BY_ID_QUERY = "DELETE FROM PROMOCODES WHERE ID = ?";

    public void create(PromoCode code)
    {
        String sql = INSERT_QUERY;
        jdbcTemplate.update(sql,code.getCode(),code.getDiscount(),code.getStartDate(),code.getExpirationDate(),code.getUsed());
    }

    public void update(PromoCode code)
    {
        String sql = UPDATE_QUERY;
        jdbcTemplate.update(sql, code.getCode());
    }

    public PromoCode getById(int id)
    {
        List<PromoCode> codes;
        String sql = SELECT_BY_ID_QUERY;
        codes = jdbcTemplate.query(sql, new PromoCodeRowMapper(),id);
        return (codes == null || codes.size() == 0) ? null : codes.get(0);
    }

    public PromoCode getByCode(String code)
    {
        List<PromoCode> codes;
        String sql = SELECT_BY_CODE_QUERY;
        codes = jdbcTemplate.query(sql, new PromoCodeRowMapper(),code);
        return (codes == null || codes.size() == 0) ? null : codes.get(0);
    }

    public List<PromoCode> getAll()
    {
        String sql = SELECT_ALL_QUERY;
	    return jdbcTemplate.query(sql, new PromoCodeRowMapper());
    }

    public List<PromoCode> getAllSortedBy(String orderBy, boolean showExpired)
    {
        String query = "SELECT * FROM PROMOCODES";
        if (!showExpired)
            query += " WHERE EXPIRATION_DATE > SYSDATE";
        query += " ORDER BY " + orderBy;
        return jdbcTemplate.query(query, new PromoCodeRowMapper());
    }

    public void deleteById(int id)
    {
        String sql = DELETE_BY_ID_QUERY;
        jdbcTemplate.update(sql,id);
    }

    private static final class PromoCodeRowMapper implements ParameterizedRowMapper<PromoCode>
    {
        public PromoCode mapRow(ResultSet rs, int rowNum) throws SQLException
        {
            PromoCode code = new PromoCode();
            code.setId(rs.getInt("id"));
            code.setCode(rs.getString("code"));
            code.setExpirationDate(rs.getDate("expiration_date"));
            code.setStartDate(rs.getDate("start_date"));
            code.setDiscount(rs.getInt("discount"));
            code.setUsed(rs.getInt("used"));
            return code;
        }
    }
}
